﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace ALDIOrganizer
{
    
    public static partial class SQLiteAccess
    {
        internal static bool IsEmpty
        {
            get
            {
                return _dataset.Tables[0].Rows.Count == 0;
            }
        }
        

        /// <summary>
        /// gets 
        /// </summary>
        /// <remarks>
        /// This is a powerful, generic fuction which generates an
        /// 
        /// It has extension methods - Any and FirstLitUp
        /// </remarks>
        /// <param Name="source">datarows </param>
        /// <param Name="EmployeeID"></param>
        /// <param Name="date"></param>
        /// <param Name="startDateFIELD"></param>
        /// <param Name="endDateFIELD"></param>
        /// <param Name="flagFIELDS"></param>
        /// <returns></returns>        


        /// <summary>
        /// returns an array of GetEmployeeIDs of all emps_from_database employed in year "year"
        /// </summary>
        /// <param Name="year">year in question</param>
        /// <returns>array of GetEmployeeIDs - records' primary keys in the CurrentFile</returns>
        internal static Int64[] EmployedInYear(int year)
        {
            var results = new List<Int64>();
            var employees = from employee in _dataset.Tables[0].AsEnumerable()
                            orderby 
                            employee.Field<string>("lastName"),
                            employee.Field<string>("firstName")
                            select employee.Field<Int64>("id");
            
            // could be more concise, but i preferred to make it understandable
            foreach (Int64 id in employees)
            {
                var qualifies = false;

                var employedDuringThatYear = (from contract in _dataset.Tables[1].AsEnumerable()
                                            where contract.Field<Int64>("employeeID") == id
                                            && contract.Field<bool>("hired")
                                            && contract.Field<DateTime>("date").Year == year
                                            select contract).Any();
                if (employedDuringThatYear)
                {
                    qualifies = true;
                }
                else
                {
                    var employedOnFirstOfJanuary = (from contract in _dataset.Tables[1].AsEnumerable()
                                                    where contract.Field<Int64>("employeeID") == id
                                                    && contract.Field<DateTime>("date") < new DateTime(year, 1, 1)
                                                    select contract.Field<bool>("hired")).LastOrDefault();
                    if (employedOnFirstOfJanuary) // LastOrDefault ensured that null will become false (default(bool))
                    {
                        qualifies = true;
                    }                    
                }
                if (qualifies)
                {
                    results.Add(id);
                }
            }    
            return results.ToArray();
        }

        internal static DataRow[] GetEmployees()
        {
            var employees = from employee in _dataset.Tables[0].AsEnumerable()
                            select employee;
            return employees.ToArray();            
        }

        internal static Int64[] GetEmployeeIDs(Int64 i)
        {
            if (i > -1) return new Int64[] { i };
            else
                return (from row in GetEmployees()
                        select row.AsEmployee().ID).ToArray();
        }

        private static DataRow[] GetRows(string fieldID, string dateID, int tableID, Int64 empID)
        {
            if (dateID != String.Empty)
            {
                return (from record in _dataset.Tables[tableID].AsEnumerable()
                        where record.Field<Int64>(fieldID) == empID
                        orderby record.Field<DateTime>(dateID)
                        select record).ToArray();
            }
            // if dateID == String.Empty
            return (from record in _dataset.Tables[tableID].AsEnumerable()
                    where record.Field<Int64>(fieldID) == empID
                    select record).ToArray();                        
        }

        internal static DataRow[] GetContracts(Int64 empID)
        {
            return GetRows("employeeID", "date", 1, empID);
        }

        internal static DataRow[] GetHolidayss(Int64 empID)
        {
            return GetRows("employeeID", "startDate", 2, empID);
        }

        internal static DataRow[] GetRequests(Int64 empID)
        {
            return GetRows("employeeID", "startDate", 3, empID);
        }

        #region retrieving records from datatables
        public static DataRow GetRecord(int tableID, Int64 recordID)
        {
            try
            {
                var record = from row in _dataset.Tables[tableID].AsEnumerable()
                             where row.Field<Int64>("id") == recordID
                             select row;
                var count = record.Count();
                if (count > 1)
                {
                    throw new Exception("Duplicate records in DataTable " + tableID.ToString() + "! record ID " +
                                        recordID.ToString() + ", " + count.ToString() + " occurences detected. ");
                }
                return record.FirstOrDefault();
            }
            catch (DeletedRowInaccessibleException)
            {
                return null;
            }
        }

        public static DataRow GetEmployee(Int64 ID)
        {
            return GetRecord(0, ID);
        }

        public static string GetEmployeeFirstName(Int64 ID)
        {
            var emp = GetEmployee(ID) == null ?
                null :
                GetEmployee(ID)[1];
            if (emp == null)
            {
                return String.Empty;
            }
            var anybodySameName = (from employee in
                                       (from row in GetEmployees()
                                        select row.AsEmployee())
                                   where employee.ID != ID &&
                                   employee.firstName == emp.ToString()
                                   select employee).Any();
            return anybodySameName ?
                GetEmployeeFullName(ID) :
                emp.ToString();
        }

        public static string GetEmployeeFullName(Int64 ID)
        {
            var emp = GetEmployee(ID);
            return String.Concat(
                emp[1],
                " ",
                emp[2]);
        }

        public static string[] GetEmployeePhoneNumbers(Int64 pID)
        {
            var emp = GetEmployee(pID);
            var numbers = new string[2];
            numbers[0] = emp.AsEmployee().mobile;            
            numbers[1] = emp.AsEmployee().phone;            
            return numbers;
        }

        public static DataRow GetContract(Int64 ID)
        {
            return GetRecord(1, ID);
        }

        public static DataRow GetHolidays(Int64 ID)
        {
            return GetRecord(2, ID);
        }

        public static DataRow GetRequest(Int64 ID)
        {
            return GetRecord(3, ID);
        }
        #endregion


                

        

        


    }
}
